Skip Headers

Oracle® Data Provider for .NET Developer's Guide 10g Release 2 (10.2) Part Number B14307-01 |
Home | Book List | Contents | Index | Master Index | Feedback

Previous | Next
| View PDF

# Connecting to Oracle Database

This section describes `OracleConnection` provider-specific features, including:

- Connection String Attributes

- Connection Pooling

- Connection Pooling for a Real Application Clusters (RAC) Database

- Operating System Authentication

- Privileged Connections

- Password Expiration

- Proxy Authentication

- Client Identifier

- Transparent Application Failover (TAF) Callback Support

## Connection String Attributes

Table 3-1 lists the supported connection string attributes.

Table 3-1 Supported Connection String Attributes

Connection String Attribute | Default Value | Description
--- | --- | ---
`Connection Lifetime` | `0` | Maximum life time (in seconds) of the connection.
`Connection Timeout` | `15` | Maximum time (in seconds) to wait for a free connection from the pool.
`Context` `Connection` | `false` | Returns an implicit database connection if set to `true`. Supported in a .NET stored procedure only
`Data` `Source` | empty string | Oracle Net Services Name, Connect Descriptor, or an easy connect naming that identifies the database to which to connect.
`DBA Privilege` | empty string | Administrative privileges: `SYSDBA` or `SYSOPER`.
`Decr Pool Size` | `1` | Number of connections that are closed when an excessive amount of established connections are unused.
`Enlist` | `true` | Serviced components automatically enlist in distributed transactions.
`HA` `Events` | `false` | Enables ODP.NET connection pool to proactively remove connections from the pool when a RAC service, service member, or node goes down.
`Load` `Balancing` | `false` | Enables ODP.NET connection pool to balance work requests across RAC instances based on the load balancing advisory and service goal.
`Incr Pool Size` | `5` | Number of new connections to be created when all connections in the pool are in use.
`Max Pool Size` | `100` | Maximum number of connections in a pool.
`Min` `Pool` `Size` | `1` | Minimum number of connections in a pool.
`Password` | empty string | Password for the user specified by `User` `Id`.
`Persist` `Security` `Info` | `false` | Retrieval of the password in the connection string.
`Pooling` | `true` | Connection pooling.
`Proxy` `User` `Id` | empty string | User name of the proxy user.
`Proxy` `Password` | empty string | Password of the proxy user.
`Statement` `Cache` `Purge` | `false` | Statement cache purged when the connection goes back to the pool.
`Statement` `Cache` `Size` | `0` | Statement cache enabled and cache size, that is, the maximum number of statements that can be cached.
`User` `Id` | empty string | Oracle user name.
`Validate` `Connection` | `false` | Validation of connections coming from the pool.

The following example uses connection string attributes to connect to Oracle Database:

    // C#

    using System;
    using Oracle.DataAccess.Client;

    class ConnectionSample
    {
      static void Main()
      {
        OracleConnection con = new OracleConnection();

        //using connection string attributes to connect to Oracle Database
        con.ConnectionString = "User Id=scott;Password=tiger;Data Source=oracle";
        con.Open();
        Console.WriteLine("Connected to Oracle" + con.ServerVersion);

        // Close and Dispose OracleConnection object
        con.Close();
        con.Dispose();
        Console.WriteLine("Disconnected");
      }
    }

See Also: "OracleConnection Properties" for detailed information on connection attributes "OracleCommand Object" for detailed information on statement caching

## Specifying the Data Source Attribute

This section describes different ways of specifying the data source attribute.

The following example shows a connect descriptor mapped to a TNS alias called `sales` in the `tnsnames.ora` file:

    sales=
     (DESCRIPTION=
      (ADDRESS= (PROTOCOL=tcp)(HOST=sales-server)(PORT=1521))
      (CONNECT_DATA=
         (SERVICE_NAME=sales.us.acme.com)))

### Using the TNS Alias

To connect as `scott/tiger` using the TNS Alias, a valid connection appears as follows:

    "user id=scott;password=tiger;data source=sales";

### Using the Connect Descriptor

ODP.NET also allows applications to connect without the use of the `tnsnames.ora` file. To do so, the entire connect descriptor can be used as the `"data` `source"`.

The connection string appears as follows:

    "user id=scott;password=tiger;data source=" +
         "(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)" +
         "(HOST=sales-server)(PORT=1521))(CONNECT_DATA="+
         "(SERVICE_NAME=sales.us.acme.com)))"

### Using Easy Connect Naming Method

The easy connect naming method enables clients to connect to a database without any configuration.

Prior to using the easy connect naming method, make sure that `EZCONNECT` is specified by the `NAMES.DIRECTORY_PATH` parameter in the `sqlnet.ora` file as follows:

    NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)

With this enabled, ODP.NET allows applications to specify the `"Data` `Source"` attribute in the form of:

    //host:[port]/[service_name]

Using the same example, some valid connection strings follow:

    "user id=scott;password=tiger;data source=//sales-server:1521/sales.us.acme.com"
    "user id=scott;password=tiger;data source=//sales-server/sales.us.acme.com"
    "user id=scott;password=tiger;data source=sales-server/sales.us.acme.com"

If the port number is not specified, 1521 is used by default.

See Also: Oracle Net Services Administrator's Guide for details and requirements in the section Using Easy Connect Naming Method

## Connection Pooling

ODP.NET connection pooling is enabled and disabled using the `Pooling` connection string attribute. By default, connection pooling is enabled. The following are `ConnectionString` attributes that control the behavior of the connection pooling service:

- `Connection` `Lifetime`

- `Connection` `Timeout`

- `Decr` `Pool` `Size`

- `HA` `Events`

- `Incr` `Pool` `Size`

- `Load` `Balancing`

- `Max` `Pool` `Size`

- `Min` `Pool` `Size`

- `Pooling`

- `Validate` `Connection`

Connection Pooling Example

The following example opens a connection using `ConnectionString` attributes related to connection pooling.

    // C#

    using System;
    using Oracle.DataAccess.Client;

    class ConnectionPoolingSample
    {
      static void Main()
      {
        OracleConnection con = new OracleConnection();

        //Open a connection using ConnectionString attributes
        //related to connection pooling.
        con.ConnectionString =
          "User Id=scott;Password=tiger;Data Source=oracle;" +
          "Min Pool Size=10;Connection Lifetime=120;Connection Timeout=60;" +
          "Incr Pool Size=5; Decr Pool Size=2";
        con.Open();
        Console.WriteLine("Connection pool successfully created");

        // Close and Dispose OracleConnection object
        con.Close();
        con.Dispose();
        Console.WriteLine("Connection is placed back into the pool.");
      }
    }

### Using Connection Pooling

When connection pooling is enabled (the default), the `Open` and `Close` methods of the `OracleConnection` object implicitly use the connection pooling service, which is responsible for pooling and returning connections to the application.

The connection pooling service creates connection pools by using the `ConnectionString` property as a signature, to uniquely identify a pool.

If there is no existing pool with the exact attribute values as the `ConnectionString` property, the connection pooling service creates a new connection pool. If a pool already exists with the requested signature, a connection is returned to the application from that pool.

When a connection pool is created, the connection pooling service initially creates the number of connections defined by the `Min` `Pool` `Size` attribute of the `ConnectionString` property. This number of connections is always maintained by the connection pooling service for the connection pool.

At any given time, these connections are in use by the application or are available in the pool.

The `Incr` `Pool` `Size` attribute of the `ConnectionString` property defines the number of new connections to be created by the connection pooling service when more connections are needed in the connection pool.

When the application closes a connection, the connection pooling service determines whether or not the connection lifetime has exceeded the value of the `Connection` `Lifetime` attribute. If so, the connection pooling service closes the connection; otherwise, the connection goes back to the connection pool. The connection pooling service enforces the `Connection` `Lifetime` only when a connection is going back to the connection pool.

The `Max` `Pool` `Size` attribute of the `ConnectionString` property sets the maximum number of connections for a connection pool. If a new connection is requested, but no connections are available and the limit for `Max` `Pool` `Size` has been reached, then the connection pooling service waits for the time defined by the `Connection` `Timeout` attribute. If the `Connection` `Timeout` time has been reached, and there are still no connections available in the pool, the connection pooling service raises an exception indicating that the connection pool request has timed-out.

The `Validate` `Connection` attribute validates connections coming out of the pool. This attribute should be used only when absolutely necessary, because it causes a round-trip to the database to validate each connection immediately before it is provided to the application. If invalid connections are uncommon, developers can create their own event handler to retrieve and validate a new connection, rather than using the `Validate` `Connection` attribute. This generally provides better performance.

The connection pooling service closes connections when they are not used; connections are closed every 3 minutes. The `Decr` `Pool` `Size` attribute of the `ConnectionString` property provides connection pooling service for the maximum number of connections that can be closed every 3 minutes.

## Connection Pooling for a Real Application Clusters (RAC) Database

This section discusses optimization and other aspects of connection pooling for a Real Application Clusters (RAC) database. RAC is the technology that makes grids possible for Oracle database by providing the ability to access the database from multiple instances, each running on nodes in a cluster.

### Connection Pool Optimizations for RAC

Oracle Data Provider for .NET optimizes connection pooling for RAC database by balancing work requests across RAC instances, based on the load balancing advisory and service goal. Furthermore, the ODP.NET connection pool can be enabled to proactively free resources associated with connections that have been severed due to a down RAC service, service member, or node.

Oracle Data Provider for .NET uses the following features to optimize connection pooling for RAC:

- Runtime Connection Load Balancing

  When Runtime Connection Load Balancing is enabled:

  - The ODP.NET connection pool dispenses connections based on the load balancing advisory and service goal.

  - The ODP.NET connection pool also balances the number of connections to each service member providing the service, based on the load balancing advisory and service goal.

  By default, this feature is disabled. To enable runtime connection load balancing, include `"Load Balancing=true"` in the connection string. This feature can only be used against a RAC database and only if `"pooling=true"`.

  In order to use Runtime Connection Load Balancing, specific RAC configurations must be set. For further information, see Oracle Database Oracle Clusterware and Oracle Real Application Clusters Administration and Deployment Guide. Oracle Net Services should also be configured for load balancing. See Oracle Net Services Administrator's Guide for further details.

  The following connection string example enables Runtime Connection Load Balancing:

      "user id=scott;password=tiger;data source=erp;load balancing=true;"

  See Also: "Supported Connection String Attributes" Oracle Database Oracle Clusterware and Oracle Real Application Clusters Administration and Deployment Guide Oracle Net Services Administrator's Guide. Oracle Call Interface Programmer's Guide Oracle Database JDBC Developer's Guide and Reference

- HA events

  When HA events is enabled:

  - ODP.NET connection pool proactively removes connections from the pool when a RAC service, service member, or node goes down.

  - ODP.NET establishes connections to existing RAC instances if the removal of severed connections bring the total number of connections below the `"min` `pool` `size"`.

  By default this feature is disabled. To enable HA events, include `"HA` `Events=true"` in the connection string. This feature can only be used against a RAC database and only if `"pooling=true"`.

  Note: The database service being connected to must be configured for `AQ_HA_NOTIFICATIONS`. For more details, see Oracle Database Oracle Clusterware and Oracle Real Application Clusters Administration and Deployment Guide

  The following connection string example enables HA Events:

      "user id=scott;password=tiger;data source=erp;HA events=true;"

  See Also: "Supported Connection String Attributes" Oracle Database Oracle Clusterware and Oracle Real Application Clusters Administration and Deployment Guide

### Pool Size Attributes in a RAC Database

When connection pools are created for a non-RAC database, pool size attributes are applied to the single service. Similarly, when connection pools are created for a RAC database, the pool size attributes are applied to a service and not to service members. For example, if `"Min` `Pool` `Size"` is set to `N`, ODP.NET does not create `N` connections for each service member. Instead, it creates, at minimum, `N` connections for the entire service, where `N` connections are distributed among the service members.

The following pool size connection string attributes are applied to a service.

- `Min` `Pool` `Size`

- `Max` `Pool` `Size`

- `Incr` `Pool` `Size`

- `Decr` `Pool` `Size`

## Operating System Authentication

Oracle Database can use Windows user login credentials to authenticate database users. To open a connection using Windows user login credentials, the `User` `Id` `ConnectionString` attribute must be set to a slash (`/)`. If the `Password` attribute is provided, it is ignored.

Note: Operating System Authentication is not supported in a .NET stored procedure.

The following example shows the use of operating system authentication:

    /* Create an OS-authenticated user in the database
       Assume init.ora has OS_AUTHENT_PREFIX set to "" and <OS_USER>
       is any valid OS or DOMAIN user.

         create user <OS_USER> identified externally;
         grant connect, resource to <OS_USER>;

       Login through OS Authentication and execute the sample.  See Oracle
       documentation for details on how to configure an OS-Authenticated user
    */

    // C#

    using System;
    using Oracle.DataAccess.Client;

    class OSAuthenticationSample
    {
      static void Main()
      {
        OracleConnection con = new OracleConnection();

        //Establish connection using OS Authentication
        con.ConnectionString = "User Id=/;Data Source=oracle;";
        con.Open();
        Console.WriteLine("Connected to Oracle" + con.ServerVersion);

        // Close and Dispose OracleConnection object
        con.Close();
        con.Dispose();
        Console.WriteLine("Disconnected");
      }
    }

See Also: Oracle Database Platform Guide for Windows for information on how to set up Oracle Database to authenticate database users using Windows user login credentials

## Privileged Connections

Oracle allows database administrators to connect to Oracle Database with either `SYSDBA` or `SYSOPER` privileges. This is done through the `DBA` `Privilege` attribute of the `ConnectionString` property.

The following example connects `scott`/`tiger` as `SYSDBA`:

    // C#

    using System;
    using Oracle.DataAccess.Client;

    class PrivilegedConnectionSample
    {
      static void Main()
      {
        OracleConnection con = new OracleConnection();

        //Connect scott/tiger as SYSDBA
        con.ConnectionString = "User Id=scott;Password=tiger;" +
          "DBA Privilege=SYSDBA;Data Source=oracle;";
        con.Open();
        Console.WriteLine("Connected to Oracle" + con.ServerVersion);

        // Close and Dispose OracleConnection object
        con.Close();
        con.Dispose();
        Console.WriteLine("Disconnected");
      }
    }

See Also: "`DBA Privilege`" for further information on privileged connections in the database

## Password Expiration

Oracle allows users passwords to expire. ODP.NET lets applications handle the password expiration by providing a new method, `OpenWithNewPassword`, that opens the connection with a new password.

The following example uses the `OracleConnection` `OpenWithNewPassword` method to connect with a new password of `panther`:

    /* Database Setup
    connect / as sysdba;
    drop user testexpire cascade;
    -- create user "testexpire" with password "testexpire"
    grant connect , resource to testexpire identified by testexpire;
    alter user testexpire password expire;
    */

    // C#

    using System;
    using Oracle.DataAccess.Client;

    class PasswordExpirationSample
    {
      static void Main()
      {
        OracleConnection con = new OracleConnection();

        try
        {
          con.ConnectionString =
            "User Id=testexpire;Password=testexpire;Data Source=oracle";
          con.Open();
          Console.WriteLine("Connected to Oracle" + con.ServerVersion);
        }
        catch (OracleException ex)
        {
          Console.WriteLine(ex.Message);

          //check the error number
          //ORA-28001 : the password has expired
          if (ex.Number == 28001)
          {
            Console.WriteLine("\nChanging password to panther");
            con.OpenWithNewPassword("panther");
            Console.WriteLine("Connected with new password.");
          }
        }
        finally
        {
          // Close and Dispose OracleConnection object
          con.Close();
          con.Dispose();
          Console.WriteLine("Disconnected");
        }
      }
    }

Note: The `OpenWithNewPassword` method should be used only when the user password has expired, not for changing the password.

See Also: "OpenWithNewPassword"

## Proxy Authentication

With proper setup in the database, proxy authentication enables middle-tier applications to control the security by preserving database user identities and privileges, and auditing actions taken on behalf of these users. This is accomplished by creating and using a proxy database user that connects and authenticates against the database on behalf a database user (that is, the real user) or database users.

Proxy authentication also provides better scalability. If connection pooling is used in conjunction with proxy authentication, the proxy authenticated connections can be shared among different real users. This is because only the connection and session established for the proxy is cached. An additional session is created for the real user when a proxy authenticated connection is requested, but it will be destroyed appropriately when the proxy authenticated connection is placed back into the pool. This design enables the application to scale well without sacrificing security.

ODP.NET applications can use proxy authentication by setting the `"Proxy` `User` `Id"` and `"Proxy` `Password"` attributes in the connection string. The real user is specified by the `"User` `Id"` attribute. Optionally, to enforce greater security, the real user's password can be provided through the `"Password"` connection string attribute.

The following example illustrates the use of ODP.NET proxy authentication:

    /*  Log on as DBA (SYS or SYSTEM) that has CREATE USER privilege.
        Create a proxy user and modified scott to allow proxy connection.

         create user appserver identified by eagle;
         grant connect, resource to appserver;
         alter user scott grant connect through appserver;
    */

    // C#

    using System;
    using Oracle.DataAccess.Client;

    class ProxyAuthenticationSample
    {
      static void Main()
      {
        OracleConnection con = new OracleConnection();

        // Connecting using proxy authentication
        con.ConnectionString = "User Id=scott;Password=tiger;" +
          "Data Source=oracle;Proxy User Id=appserver;Proxy Password=eagle; ";
        con.Open();
        Console.WriteLine("Connected to Oracle" + con.ServerVersion);

        // Close and Dispose OracleConnection object
        con.Close();
        con.Dispose();
        Console.WriteLine("Disconnected");
      }
    }

See Also: Oracle Database Application Developer's Guide - Fundamentals for details on designing a middle-tier server using proxy users Oracle Database SQL Reference for the description and syntax of the proxy clause for the `ALTER` `USER` statement Oracle Database Security Guide section "Standard Auditing in a Multitier Environment"

## Client Identifier

The client identifier is a predefined attribute from the Oracle application context namespace `USERENV`. It is similar to proxy authentication because it can enable tracking of user identities. However, client identifier does not require the creation of two sessions (one for the proxy user and another for the end user) as proxy authentication does. In addition, the client identifier does not have to be a database user. It can be set to any string. But most importantly, by using client identifier, ODP.NET developers can use application context and Oracle Label Security, and configure Oracle Virtual Private Database (VPD) more easily. To set the client identifier, ODP.NET applications can set the `ClientId` property on the `OracleConnection` object after opening a connection. If connection pooling is enabled, the `ClientId` is reset to `null` whenever a connection is placed back into the pool.

ODP.NET exposes the `ClientId` property on the `OracleConnection` object. Setting the `ClientId` property internally sets the `CLIENT_IDENTIFIER` attribute on the session. To clear the `ClientId` property, simply set it to `""` or `string.Empty`. The `ClientId` property is write-only.

See Also: "ClientId" Oracle Database Security Guide

## Transparent Application Failover (TAF) Callback Support

Transparent Application Failover (TAF) is a feature in Oracle Database that provides high availability.

Note: TAF is not supported in a .NET stored procedure.

TAF enables an application connection to automatically reconnect to another database instance if the connection gets severed. Active transactions roll back, but the new database connection, made by way of a different node, is identical to the original. This is true regardless of how the connection fails.

With TAF, a client notices no loss of connection as long as there is one instance left serving the application. The database administrator controls which applications run on which instances, and also creates a failover order for each application.

When a session fails over to another database, the NLS settings that were initially set on the original session are not carried over to the new session. Therefore, it is the responsibility of the application to set these NLS settings on the new session.

### TAF Notification

Given the delays that failovers can cause, applications may wish to be notified by a TAF callback. ODP.NET supports the TAF callback function through the `Failover` event of the `OracleConnection` object, which allows applications to be notified whenever a failover occurs. To receive TAF callbacks, an event handler function must be registered with the `Failover` event.

### When Failover Occurs

When a failover occurs, the `Failover` event is raised and the registered event handler is invoked several times during the course of reestablishing the connection to another Oracle instance.

The first call to the event handler occurs when Oracle Database first detects an instance connection loss. This allows the application to act accordingly for the upcoming delay for the failover.

If the failover is successful, the `Failover` event is raised again when the connection is reestablished and usable. At this time, the application can resynchronize the `OracleGlobalization` session setting and inform the application user that a failover has occurred.

If failover is unsuccessful, the `Failover` event is raised to inform the application that a failover did not take place.

The application can determine whether or not the failover is successful by checking the `OracleFailoverEventArgs` object that is passed to the event handler.

### Registering an Event Handler for Failover

The following example registers an event handler method called `OnFailover`:

    // C#

    using System;
    using Oracle.DataAccess.Client;

    class TAFCallBackSample
    {
      public static FailoverReturnCode OnFailover(object sender,
                                                  OracleFailoverEventArgs eventArgs)
      {
        switch (eventArgs.FailoverEvent)
        {
          case FailoverEvent.Begin :
            Console.WriteLine(
              " \nFailover Begin - Failing Over ... Please standby \n");
            Console.WriteLine(
              " Failover type was found to be " + eventArgs.FailoverType);
            break;

          case FailoverEvent.Abort :
            Console.WriteLine(" Failover aborted. Failover will not take place.\n");
            break;

          case FailoverEvent.End :
            Console.WriteLine(" Failover ended ...resuming services\n");
            break;

          case FailoverEvent.Reauth :
            Console.WriteLine(" Failed over user. Resuming services\n");
            break;

          case FailoverEvent.Error :
            Console.WriteLine(" Failover error gotten. Sleeping...\n");
            return FailoverReturnCode.Retry;

          default :
            Console.WriteLine("Bad Failover Event: %d.\n", eventArgs.FailoverEvent);
            break;
        }
        return FailoverReturnCode.Success;
      } /* OnFailover */

      static void Main()
      {
        OracleConnection con = new OracleConnection();

        con.ConnectionString = "User Id=scott;Password=tiger;Data Source=oracle;";
        con.Open();
        con.Failover += new OracleFailoverEventHandler(OnFailover);
        Console.WriteLine("Event Handler is successfully registered");

        // Close and Dispose OracleConnection object
        con.Close();
        con.Dispose();
      }
    }

The `Failover` event invokes only one event handler. If multiple `Failover` event handlers are registered with the `Failover` event, only the event handler registered last is invoked.

Note: Distributed transactions are not supported in an environment where failover is enabled.

See Also: Oracle Net Services Administrator's Guide "OracleFailoverEventHandler Delegate" "OracleFailoverEventArgs Class"